using System;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    /**
     * Classname : HocVien
     * Version information :1.0
     * Date  :26-09-2006
     * Copyright notice : 
     */

    public class DAHocVienLV2: DbObject
    {
        protected SqlConnection connec;

        public DAHocVienLV2(SqlConnection Connection)
        {
            connec = Connection;
        }
        // return all the HocViens select Language
        // Danh cho nguoi quan tri
        public int loadDemSoHocVienLV2ByTrangThai( int TrangThaiID)
        {
            int numAffected;
            SqlParameter[] parameters = {   new SqlParameter("@TrangThaiID", SqlDbType.Int, 4) 
                                        };
            parameters[0].Value = TrangThaiID;

            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVienLV2_loadDemSoHocVienLV2ByTrangThai", parameters, "TB_L2", connec);
            if (dsHocVien != null && dsHocVien.Tables[0].Rows.Count > 0)
            {
                numAffected = Convert.ToInt32(dsHocVien.Tables[0].Rows[0]["Tong"].ToString());
            }
            else
            {
                numAffected = 0;
            }
            return numAffected;
        }

        public DataSet loadHocVienLV2()
        {
            // create the parameter
            DataSet dsHocVien = RunProcedure("sp_HocVienLV2_loadLV2", "TB_L2", connec);
            for (int i = 0; i < dsHocVien.Tables[0].Rows.Count; i++)
            {
                dsHocVien.Tables[0].Rows[i]["Seq"] = (i + 1).ToString();
            }
            return dsHocVien;
        }

        // return only the record with the specified ID!!!!!!!!!!!
        public DataRow loadHocVienLV2ID(int HocVienID, int LevelID)
        {
            // create the parameter
            SqlParameter[] parameters = {   new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                            new SqlParameter("@LevelID", SqlDbType.Int, 4)
                                        };
            parameters[0].Value = HocVienID;
            parameters[1].Value = LevelID;

            using (DataSet HocViens = RunProcedure("sp_HocVienLV2_loadLV2ID", parameters, "TB_L2", connec))
            {
                return HocViens.Tables[0].Rows[0];
            }
        }
	
        // add a new question
        // Them ban ghi hien hanh
        public int insertHocVienLV2(int HocVienID, string CaoDang, string DaiHoc, string PTTH, string TrungCap,
                    string KhacTotNghiep, bool QuanTriKinhDoanh, bool KeToan, bool TinHocUngDung, bool TaiChinhNganHang,bool LuatKinhTe,
                    string KhacLayBang,int TrangThaiID, int NguoiDungID, DateTime NgayGoi,int LevelID,
                  string NoiDungTaiSao, int TrangThaiGoiID, string NoiDungNgayGoi)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters =  {	    new SqlParameter("@ID", SqlDbType.Int ,4),
                                                new SqlParameter("@HocVienID", SqlDbType.Int, 4),
                                                new SqlParameter("@CaoDang", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@DaiHoc", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@PTTH", SqlDbType.NVarChar,256 ),
                                                new SqlParameter("@TrungCap", SqlDbType.NVarChar, 256),
                                                new SqlParameter("@KhacTotNghiep", SqlDbType.NVarChar, 512),
                                                new SqlParameter("@QuanTriKinhDoanh", SqlDbType.Bit, 1),
                                                new SqlParameter("@KeToan", SqlDbType.Bit ,1),
                                                new SqlParameter("@TinHocUngDung", SqlDbType.Bit, 1),
                                                new SqlParameter("@TaiChinhNganHang", SqlDbType.Bit ,1),
                                                new SqlParameter("@LuatKinhTe", SqlDbType.Bit ,1),
                                                new SqlParameter("@KhacLayBang", SqlDbType.NVarChar ,512),
                                              
										        new SqlParameter("@TrangThaiID", SqlDbType.Int, 4),
                                                new SqlParameter("@NguoiDungID", SqlDbType.Int, 4),	
                                                new SqlParameter("@NgayGoi", SqlDbType.DateTime)	,	
                                                new SqlParameter("@LevelID", SqlDbType.Int, 4)	,	
                                              
                                                new SqlParameter("@NoiDungTaiSao", SqlDbType.NVarChar, 2000)	,
                                                new SqlParameter("@TrangThaiGoiID", SqlDbType.Int, 4),
                                              
                                                new SqlParameter("@NoiDungNgayGoi", SqlDbType.NVarChar, 2000)
										 };
            // set the values
            parameters[1].Value = HocVienID;
            parameters[2].Value = CaoDang;
            parameters[3].Value = DaiHoc.Trim();
            parameters[4].Value = PTTH.Trim();
            parameters[5].Value = TrungCap;
            parameters[6].Value = KhacTotNghiep.Trim();
            parameters[7].Value = QuanTriKinhDoanh;
            parameters[8].Value = KeToan;
            parameters[9].Value = TinHocUngDung;
            parameters[10].Value = TaiChinhNganHang;
            parameters[11].Value = LuatKinhTe;
            parameters[12].Value = KhacLayBang;
          
            parameters[13].Value = TrangThaiID;
            parameters[14].Value = NguoiDungID;
            parameters[15].Value = NgayGoi;
            parameters[16].Value = LevelID;
         
            parameters[17].Value = NoiDungTaiSao;
            parameters[18].Value = TrangThaiGoiID;
    
            parameters[19].Value = NoiDungNgayGoi;
            parameters[0].Direction = ParameterDirection.Output;

            // run the procedure
            RunProcedure("sp_HocVienLV2_insertLV2Main_cong_test", parameters, out numAffected, connec);

           // return (int)parameters[0].Value;
            return numAffected;
        }
        // Xoa Role
        public int removeHocVienLV2(int HocVienID)
        {
            int numAffected;
            // create the parameters
            SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int) };
            parameters[0].Value = HocVienID;
            // run the procedure
            RunProcedure("sp_HocVienLV2_removeLV2", parameters, out numAffected, connec);
            return numAffected;
        }
    }
}